﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace ASPNETX_SE
{
    public class DatabaseHelper
    {
        public string Machine { get; set; }
        public string Database { get; set; }
        public string Username { get; set; }
        public string Password { get; set; }

        private bool IsDBOpened = false;

        private SqlConnection conn;
        private SqlCommand comm;

        public DatabaseHelper()
        { 
            
        }

        public void Open()
        {
            string strconn = "Data Source="+Machine+";Initial Catalog="+Database+";Persist Security Info=True;User ID="+Username+";Password="+Password;
            conn = new SqlConnection(strconn);
            try
            {
                if (IsDBOpened == false)
                {
                    conn.Open();
                    IsDBOpened = true;
                }

            }
            catch{}
        }

        /// <summary>
        /// 获得当前库下所有表
        /// </summary>
        /// <returns></returns>
        public List<string> GetAllTableNames()
        {
            List<string> result = new List<string>();

            string strsql = "select name from sysobjects where xtype='u'";
            comm = new SqlCommand(strsql, conn);
            SqlDataReader dr = comm.ExecuteReader();

            while (dr.Read())
            {
                result.Add(dr["name"].ToString());
            }

            dr.Close();
            

            return result;
        }

        /// <summary>
        /// 获得某表下的所有列
        /// </summary>
        /// <returns></returns>
        public List<string> GetAllColumnsInTable(string pstrtable)
        {
            List<string> result = new List<string>();

            string strsql = "select name from syscolumns where id=(select max(id) from sysobjects where xtype='u' and name='" + pstrtable + "')";
            comm = new SqlCommand(strsql, conn);
            SqlDataReader dr = comm.ExecuteReader();

            while (dr.Read())
            {
                result.Add(dr["name"].ToString());
            }

            dr.Close();

            return result;
        }
    }
}

//--读取库中的所有表名
//select name from sysobjects where xtype='u'

//--读取指定表的所有列名
//select name from syscolumns where id=(select max(id) from sysobjects where xtype='u' and name='表名')
